﻿/****************************************************************
* MACHINE: DESKTOP-I6HF1HG
* NAME: gjp
* CREATEDATE: 2019/3/4 11:03:23
* DESC: <DESCRIPTION>
* **************************************************************/
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using LEE.Tools.Data;
using LEE.Tools.Others;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;

namespace LEE.Tools.IO
{
    /// <summary>
    /// Summary description for ExcelHelper
    /// </summary>
    public static class ExcelHelper
    {
        /// <summary>
        /// Excel文档流转换成DataTable
        /// </summary>
        /// <param name="excelStream">Excel文件流</param>
        /// <param name="isXlsx">是否为.xlsx</param>
        /// <returns></returns>
        public static DataTable ExcelStreamToDataTable(Stream excelStream, bool isXlsx = true)
        {
            IWorkbook workbook;
            if (isXlsx)
            {
                workbook = new XSSFWorkbook(excelStream);
            }
            else
            {
                workbook = new HSSFWorkbook(excelStream);
            }
            var sheet = workbook.GetSheetAt(0);
            var table = new DataTable();
            var headerRow = sheet.GetRow(0);//第一行为标题行  
            var cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells  
            var rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1  

            //handling header.  
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i) == null) continue;
                var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            for (var i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
            {
                var row = sheet.GetRow(i);
                var dataRow = table.NewRow();
                if (row != null && row.Cells.Count > 0)
                {
                    for (int j = row.FirstCellNum; j < table.Columns.Count; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = GetCellValue(row.GetCell(j));
                        }
                    }
                }

                table.Rows.Add(dataRow);
            }
            return table;

        }

        /// <summary>
        /// 将Excel文件流，转换为实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="stream"></param>
        /// <param name="isXlsx"></param>
        /// <returns></returns>
        public static List<T> ExcelStreamToEntityList<T>(Stream stream, bool isXlsx = true) where T : new()
        {
            var dataTable = ExcelStreamToDataTable(stream, isXlsx);
            return dataTable.ToEntityList<T>();
        }

        /// <summary>
        /// 将列表保存到Excel数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static byte[] ToExcelData<T>(this List<T> list, string sheetName = "Sheet1") where T : class
        {
            var xlsx = new HSSFWorkbook();
            try
            {
                var type = typeof(T);
                var headers = GetColumnNames(type);
                var properties = type.GetProperties();
                var rowId = 0;

                if (headers == null || headers.Count == 0) return null;

                // 创建表头
                var sheet = xlsx.CreateSheet(sheetName);
                var row = sheet.CreateRow(rowId);
                for (var col = 0; col < headers.Count; ++col)
                {
                    row.CreateCell(col).SetCellValue(headers[col].Item1);
                }

                // 填充数据
                foreach (var item in list)
                {
                    rowId += 1;
                    row = sheet.CreateRow(rowId);
                    for (var col = 0; col < headers.Count; ++col)
                    {
                        var header = headers[col];
                        var property = properties.FirstOrDefault(x => x.Name == header.Item2);
                        var val = property == null ? "" : property.GetValue(item)?.ToString();
                        if (NetworkHelper.UrlDiscern(val))
                        {
                            row.Height = 70 * 20;
                            var bytes = FileHelper.GetBytesByUrl(val);
                            int pictureIdx = xlsx.AddPicture(bytes, PictureType.JPEG);
                            HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                            //把图片插到相应的位置
                            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, rowId, col + 1, rowId + 1);
                            HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                        }
                        else
                        {
                            row.CreateCell(col, CellType.String).SetCellValue(val);
                        }
                    }
                }

                byte[] result;
                using (var ms = new MemoryStream())
                {
                    xlsx.Write(ms);
                    result = ms.ToArray();
                }

                return result;
            }
            finally
            {
                xlsx.Close();
            }
        }

        /// <summary>
        /// 将列表保存到Excel文件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        public static void SaveToExcelFile<T>(this List<T> list, string filePath, string sheetName = "Sheet1") where T : class
        {
            var data = ToExcelData(list, sheetName);
            File.WriteAllBytes(filePath, data);
        }

        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            string cellValue = string.Empty;
            if (cell == null)
                return cellValue;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    cellValue = string.Empty;
                    break;
                case CellType.Boolean:
                    cellValue = cell.BooleanCellValue.ToString();
                    break;
                case CellType.Error:
                    cellValue = cell.ErrorCellValue.ToString();
                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        cellValue = cell.DateCellValue.ToString();
                    }
                    else
                    {
                        cellValue = cell.NumericCellValue.ToString();
                    }
                    break;
                case CellType.Unknown:
                case CellType.String:
                    cellValue = cell.StringCellValue;
                    break;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        cellValue = cell.ToString();
                    }
                    catch
                    {
                        cellValue = cell.NumericCellValue.ToString();
                    }
                    break;
                default:
                    cellValue = cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                    break;
            }
            return cellValue?.Trim();
        }

        /// <summary>
        /// 获取列标题
        /// </summary>
        /// <param name="type"></param>
        /// <param name="includeReference"></param>
        /// <returns>显示名称，真实属性名称</returns>
        private static List<Tuple<string, string>> GetColumnNames(Type type, bool includeReference = false)
        {
            var result = new List<Tuple<string, string>>();
            var properties = type.GetProperties();
            var stringType = typeof(string);
            foreach (var item in properties)
            {
                if (!(item.PropertyType.IsValueType || item.PropertyType == stringType))
                {
                    if (!includeReference)
                        continue;
                }

                var display = item.GetCustomAttribute<DisplayNameAttribute>();
                var displayName = display != null ? display.DisplayName : item.Name;
                result.Add(new Tuple<string, string>(displayName, item.Name));
            }
            return result;
        }

        public static byte[] ToExcelData(this Dictionary<string, string> items, bool includeReference = true, string sheetName = "Sheet1")
        {
            if (items == null)
            {
                return null;
            }
            var columns = new List<string>();
            var data = new List<string>();
            var stringType = typeof(string);
            foreach (var property in items)
            {
                columns.Add(property.Key);
                data.Add(property.Value);
            }
            var result = GenerateXLS(new List<List<string>>() { data }, columns, sheetName: sheetName);
            return result;
        }

        public static byte[] ToExcelData(this IEnumerable<Dictionary<string, string>> items, bool includeReference = true, string sheetName = "Sheet1")
        {
            if (items == null || !items.Any())
            {
                return null;
            }
            var columns = items.Select(i => i.Select(k => k.Key)).FirstOrDefault().ToList();
            var allData = new List<List<string>>();
            var stringType = typeof(string);
            foreach (var dic in items)
            {
                var data = new List<string>();
                foreach (var property in dic)
                {
                    data.Add(property.Value);
                }
                allData.Add(data);
            }
            var result = GenerateXLS(allData, columns, sheetName: sheetName);
            return result;
        }

        /// <summary>
        /// 生成EXCEL文件自己数组
        /// </summary>
        /// <param name="data">待保存的数据</param>
        /// <param name="header">表头，为空时则无表头</param>
        /// <param name="author"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        private static byte[] GenerateXLS(IList<List<string>> data, IList<string> header = null, string author = null, string sheetName = "Sheet1")
        {
            var xlsx = new HSSFWorkbook();
            try
            {
                var sheet = xlsx.CreateSheet(sheetName);
                var rowId = 0;
                // 创建表头
                if (header != null && 0 < header.Count)
                {
                    var row = sheet.CreateRow(rowId);
                    for (var col = 0; col < header.Count; ++col)
                    {
                        row.CreateCell(col).SetCellValue(header[col]);
                    }
                }

                //填充数据
                foreach (var rowData in data)
                {
                    rowId += 1;
                    var row = sheet.CreateRow(rowId);
                    for (var col = 0; col < rowData.Count; ++col)
                    {
                        var val = rowData[col];
                        if (NetworkHelper.UrlDiscern(val))
                        {
                            row.Height = 70 * 20;
                            var bytes = FileHelper.GetBytesByUrl(val);
                            int pictureIdx = xlsx.AddPicture(bytes, PictureType.JPEG);
                            HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                            //把图片插到相应的位置
                            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, rowId, col + 1, rowId + 1);
                            HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                        }
                        else
                        {
                            row.CreateCell(col, CellType.String).SetCellValue(val);
                        }
                    }
                }

                #region 设置作者
                if (!string.IsNullOrEmpty(author) && data.Count > 0)
                {
                    rowId += 2;
                    var rowAuthor = sheet.CreateRow(rowId);
                    var rowDate = sheet.CreateRow(++rowId);
                    rowAuthor.CreateCell(data[0].Count - 2).SetCellValue("Author:");
                    rowAuthor.CreateCell(data[0].Count - 1).SetCellValue(author);
                    rowDate.CreateCell(data[0].Count - 2).SetCellValue("Date");
                    rowDate.CreateCell(data[0].Count - 1).SetCellValue(DateTime.Now.ToString("yyyy-MM-dd HH:mm"));
                }
                #endregion

                byte[] result;
                using (var ms = new MemoryStream())
                {
                    xlsx.Write(ms);
                    result = ms.ToArray();
                }
                return result;
            }
            finally
            {
                xlsx.Close();
            }
        }
    }
}
